package com.example.mapper;

import com.example.dto.FiveValues;
import com.example.dto.FunnelValues;
import com.example.dto.OneValues;
import com.example.dto.ThreeValues;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 数据分析Mapper接口
 * 提供各类业务数据分析的数据库查询方法
 */
@Mapper
public interface AnalysisMapper {

    /**
     * 查询每日流量趋势分析数据
     * 包含每日的用户数、页面浏览量、收藏数、购物车数和购买数
     * @return FiveValues对象列表，包含日期和各项指标数据
     */
    @Select("SELECT day as name,user_cnt as value1, pv_cnt as value2,fav_cnt as value3, cart_cnt as value4, buy_cnt as value5 FROM day_traffic_trend_analysis ORDER BY day \n")
    List<FiveValues> dayTrafficTrendAnalysis();

    /**
     * 查询每小时流量趋势分析数据
     * 包含每小时的用户数、页面浏览量、收藏数、购物车数和购买数
     * @return FiveValues对象列表，包含小时和各项指标数据
     */
    @Select("SELECT hour as name,user_cnt as value1, pv_cnt as value2,fav_cnt as value3, cart_cnt as value4, buy_cnt as value5 FROM hour_traffic_trend_analysis ORDER BY hour \n")
    List<FiveValues> hourTrafficTrendAnalysis();

    /**
     * 查询流量漏斗转化率分析数据
     * 包含各环节的名称、实际值和转化率
     * @return FunnelValues对象列表，包含环节名称、实际值和转化率
     */
    @Select("SELECT name, value as real_value, rate as value FROM traffic_funnel_conversion_analysis ORDER BY value desc\n")
    List<FunnelValues> trafficFunnelConversionAnalysis();

    /**
     * 查询RFM客户分析数据
     * 基于最近购买时间、购买频率和购买金额对客户进行分类统计
     * @return OneValues对象列表，包含客户类型和对应用户数量
     */
    @Select("SELECT user_type as name, user_cnt as value  FROM rfm_customer_analysis order by user_cnt\n")
    List<OneValues> rfmCustomerAnalysis();

    /**
     * 查询用户留存分析数据
     * 包含不同时间段的用户留存率
     * @return ThreeValues对象列表，包含日期和1天、3天、5天留存率
     */
    @Select("SELECT day as name, reten_1_rate as value1, reten_3_rate as value2, reten_5_rate as value3  FROM user_retention_analysis ORDER BY day\n")
    List<ThreeValues> userRetentionAnalysis();

    /**
     * 查询购买类别排名数据
     * 按购买数量排序的前10个商品类别
     * @return OneValues对象列表，包含类别ID和购买数量
     */
    @Select("SELECT category_id as name, cnt as value FROM top10_category ORDER BY cnt DESC\n")
    List<OneValues> top10Category();

    /**
     * 查询购买商品排名数据
     * 按购买数量排序的前10个商品
     * @return OneValues对象列表，包含商品ID和购买数量
     */
    @Select("SELECT item_id as name, cnt as value FROM top10_items ORDER BY cnt DESC\n")
    List<OneValues> top10Items();
}
